# encoding: utf-8
# A18 车型导入MT码，生成插入 sql
# author yeyi
# date 2021-01-10
import time
import requests
import json
import openpyxl
import traceback

# 联调 flux 项目流量接口
#url = "http://129.204.101.173/v1/flux/query_flow"
# 预发布 flux 项目流量接口
url = "http://10.123.2.120/v1/flux/query_flow"
headers = {
    'Content-Type': "application/json",
    'CompanyID': "1"
}
# 根据 vin 查 iccid
#urlGetIccid = "http://pre-bss.gacicv.com/v1/bss/sim/getSimByVin?vin="
urlGetIccid = "http://10.123.16.111:8080/v1/bss/sim/getSimByVin?vin="

fileName = 'queryFlowByVin'

def printt(s):
    #print(s)
    f = open(fileName+'.log', 'a+', encoding='utf-8')
    print(str(s),file=f)
    f.close()

# 统一处理异常
def dealException(e):
    f = open(fileName+'.log', 'a+', encoding='utf-8')
    traceback.print_exc(file=f)
    print(str(e),file=f)
    f.close()

offset = 1
excelPath = '.\\'+fileName+'.xlsx'
def getVins():
    a = []
    # 打开excel文件,获取工作簿对象
    wb = openpyxl.load_workbook(excelPath)
    # 获取workbook中所有的表格
    sheets = wb.sheetnames
    printt(sheets)
    for sheet in sheets:
        if sheet != 'VIN':
            continue
        ws = wb[sheet]
        for i in range(2, ws.max_row + 1, offset):
            vin = ws.cell(row=i, column=1).value
            a.append(vin)
    
    if len(a)==0:
        printt(excelPath+' empty')
    return a


csvGap = ','
# 写入 csv 文件
def writeCsv(s):
    #print(s)
    f = open(fileName+'.csv', 'a+', encoding='utf-8')
    print(str(s),file=f)
    f.close()

printt('start: '+time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time())))
vins = getVins()
num = 0
csvLine = ''
for vin in vins:
    try:
        if 0!=num:
            writeCsv(csvLine)
            csvLine = ''

        num = num + 1
        if num % 100 == 0:
            printt("num: "+str(num))

        printt("vin: "+vin)
        csvLine = vin
        response = requests.get(urlGetIccid+vin)
        jsStr = str(response.content, 'utf-8')
        # print(jsStr)
        js = json.loads(jsStr)
        if 0 != js['code']:
            printt(vin+" err: "+js['msg'])
            continue
        data = js['data']
        if data is None:
            printt(vin+" data null: ")
            continue
        iccid = data['iccid']
        if len(iccid) <=0:
            printt(vin+" iccid empty: ")
            continue

        csvLine = csvLine + csvGap + iccid+csvGap

        payload = "{\"iccid\": \"" + iccid + "\",\"provider\":\"1\"}"
        # print(payload)
        response = requests.request(
            "POST", url, data=payload.encode('utf-8'), headers=headers)
        jsStr = str(response.content, 'utf-8')
        # print(jsStr)
        js = json.loads(jsStr)
        if 0 != js['code']:
            printt(vin+" err2: "+js['msg'])
            continue
        data = js['data']
        if data is None:
            printt(vin+" data2 null: ")
            continue

        csvLine = csvLine + str(data)
        printt("res: "+str(data))
    except Exception as e:
        dealException(e)
    
printt('end: '+time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time())))